
CREATE DATABASE CINEMASPL
GO
USE CINEMASPL
GO
-- Create table for theater information
CREATE TABLE theaters (
   theater_id INT PRIMARY KEY,
   name VARCHAR(100),
   location VARCHAR(100),
   total_seats INT
);

-- Create table for screen information
CREATE TABLE screens (
   screen_id INT PRIMARY KEY,
   theater_id INT,
   screen_number INT,
   FOREIGN KEY (theater_id) REFERENCES theaters(theater_id)
);

-- Create table for show information
CREATE TABLE shows (
   show_id INT PRIMARY KEY,
   movie_id INT,
   screen_id INT,
   show_time TIME,
   FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
   FOREIGN KEY (screen_id) REFERENCES screens(screen_id)
);

-- Create table for customer information
CREATE TABLE customers (
   customer_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100),
   phone VARCHAR(20)
);

-- Create table for ticket information
CREATE TABLE tickets (
   ticket_id INT PRIMARY KEY,
   show_id INT,
   customer_id INT,
   seat_number INT,
   purchase_date DATE,
   FOREIGN KEY (show_id) REFERENCES shows(show_id),
   FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Create table for employee information
CREATE TABLE employees (
   employee_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100),
   position VARCHAR(100),
   theater_id INT,
   FOREIGN KEY (theater_id) REFERENCES theaters(theater_id)
);

-- Create table for payment information
CREATE TABLE payments (
   payment_id INT PRIMARY KEY,
   ticket_id INT,
   amount DECIMAL(10,2),
   payment_method VARCHAR(20),
   FOREIGN KEY (ticket_id) REFERENCES tickets(ticket_id)
);
USE CINEMASPL
GO
-- Insert values into the movies table
INSERT INTO movies (movie_id, title, release_date, duration, genre, rating)
VALUES
   (1, 'The Shawshank Redemption', '1994-09-14', 142, 'Drama', 9.2),
   (2, 'The Godfather', '1972-03-24', 175, 'Crime', 9.2),
   (3, 'The Dark Knight', '2008-07-16', 152, 'Action', 9.0),
   (4, 'The Godfather: Part II', '1974-12-20', 200, 'Crime', 9.0),
   (5, 'The Lord of the Rings: The Return of the King', '2003-12-17', 201, 'Fantasy', 9.0);

-- Insert values into the theaters table
INSERT INTO theaters (theater_id, name, location, total_seats)
VALUES
   (1, 'AMC Lincoln Square 13', 'New York, NY', 1300),
   (2, 'Regal Cinemas E-Walk 13 & RPX', 'New York, NY', 1000),
   (3, 'ArcLight Cinemas', 'Los Angeles, CA', 800),
   (4, 'Alamo Drafthouse Cinema - New Mission', 'San Francisco, CA', 500),
   (5, 'AMC Century City 15', 'Los Angeles, CA', 1200);

-- Insert values into the screens table
INSERT INTO screens (screen_id, theater_id, screen_number)
VALUES
   (1, 1, 1),
   (2, 1, 2),
   (3, 2, 1),
   (4, 2, 2),
   (5, 3, 1);

-- Insert values into the shows table
INSERT INTO shows (show_id, movie_id, screen_id, show_time)
VALUES
   (1, 1, 1, '20:00:00'),
   (2, 2, 2, '22:00:00'),
   (3, 3, 3, '19:00:00'),
   (4, 4, 4, '21:00:00'),
   (5, 5, 5, '18:00:00');

-- Insert values into the customers table
INSERT INTO customers (customer_id, first_name, last_name, email, phone)
VALUES
   (1, 'John', 'Doe', 'johndoe@gmail.com', '123-456-7890'),
   (2, 'Jane', 'Doe', 'janedoe@gmail.com', '098-765-4321'),
   (3, 'Michael', 'Smith', 'michaelsmith@gmail.com', '111-222-3333'),
   (4, 'Emily', 'Johnson', 'emilyjohnson@gmail.com', '444-555-6666'),
   (5, 'William', 'Brown', 'williambrown@gmail.com', '777-888-9999');

-- Insert values into the tickets table
INSERT INTO tickets (ticket_id, show_id, customer_id, seat_number, purchase_date)
VALUES
   (1, 1, 1, 100, '2022-01-01'),
   (2, 2, 2, 200, '2022-02-01'),
   (3, 3, 3, 300, '2022-03-01'),
   (4, 4, 4, 400, '2022-04-01'),
   (5, 5, 5, 500, '2022-05-01');

-- Insert values into the employees table
-- Insert values into the employees table
INSERT INTO employees (employee_id, first_name, last_name, email, position, theater_id)
VALUES
   (1, 'Mark', 'Johnson', 'mark.johnson@theater.com', 'Manager', 1),
   (2, 'Sarah', 'Smith', 'sarah.smith@theater.com', 'Assistant Manager', 2),
   (3, 'Emily', 'Brown', 'emily.brown@theater.com', 'Usher', 3),
   (4, 'William', 'Davis', 'william.davis@theater.com', 'Concession Stand Worker', 4),
   (5, 'Michael', 'Wilson', 'michael.wilson@theater.com', 'Ticket Seller', 5);

-- Insert values into the payments table
INSERT INTO payments (payment_id, ticket_id, amount, payment_method)
VALUES
   (1, 1, 10.00, 'Credit Card'),
   (2, 2, 15.00, 'Cash'),
   (3, 3, 20.00, 'Credit Card'),
   (4, 4, 25.00, 'Debit Card'),
   (5, 5, 30.00, 'PayPal');
USE CINEMASPL
GO
-- Movies table
-- Insert new movie
INSERT INTO movies (movie_id, title, release_date, duration, genre, rating)
VALUES (1, 'The Matrix', '1999-03-31', 136, 'Action', 8.7);

-- Update movie rating
UPDATE movies
SET rating = 9.2
WHERE movie_id = 1;

-- Theaters table
-- Insert new theater
INSERT INTO theaters (theater_id, name, location, total_seats)
VALUES (1, 'AMC Theaters', 'New York', 500);

-- Update theater location
UPDATE theaters
SET location = 'Los Angeles'
WHERE theater_id = 1;

-- Screens table
-- Insert new screen
INSERT INTO screens (screen_id, theater_id, screen_number)
VALUES (1, 1, 1);

-- Update screen number
UPDATE screens
SET screen_number = 2
WHERE screen_id = 1;

-- Shows table
-- Insert new show
INSERT INTO shows (show_id, movie_id, screen_id, show_time)
VALUES (1, 1, 1, '19:00:00');

-- Update show time
UPDATE shows
SET show_time = '21:00:00'
WHERE show_id = 1;

-- Customers table
-- Insert new customer
INSERT INTO customers (customer_id, first_name, last_name, email, phone)
VALUES (1, 'John', 'Doe', 'john.doe@gmail.com', '555-555-5555');

-- Update customer phone number
UPDATE customers
SET phone = '555-555-5556'
WHERE customer_id = 1;

-- Tickets table
-- Insert new ticket
INSERT INTO tickets (ticket_id, show_id, customer_id, seat_number, purchase_date)
VALUES (1, 1, 1, 1, '2022-12-31');

-- Update seat number
UPDATE tickets
SET seat_number = 2
WHERE ticket_id = 1;

-- Employees table
-- Insert new employee
INSERT INTO employees (employee_id, first_name, last_name, email, position, theater_id)
VALUES (1, 'Jane', 'Doe', 'jane.doe@gmail.com', 'Manager', 1);

-- Update employee position
UPDATE employees
SET position = 'Assistant Manager'
WHERE employee_id = 1;

-- Payments table
-- Insert new payment
INSERT INTO payments (payment_id, ticket_id, amount, payment_method)
VALUES (1, 1, 20.0, 'Credit');
USE CINEMASPL
GO
SELECT * FROM movies ORDER BY release_date DESC;
SELECT first_name, last_name, email FROM customers WHERE phone LIKE '%123%';
SELECT title, show_time FROM movies
INNER JOIN shows ON movies.movie_id = shows.movie_id
WHERE genre = 'Action';
--
SELECT * FROM movies ORDER BY rating DESC;
SELECT first_name, last_name, email FROM customers WHERE phone like '%555%';
SELECT title, release_date, rating FROM movies WHERE genre='Comedy' OR genre='Action' ORDER BY release_date ASC;